package com.boss.salary.dao.impl;

import com.boss.framework.dao.BaseDaoImpl;
import com.boss.framework.dto.PaginationDTO;
import com.boss.salary.constant.TableName;
import com.boss.salary.dao.IReportByTheUnitDao;
import com.boss.salary.entity.ReportByTheUnit;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
@Repository
public class ReportByTheUnitDaoImpl extends BaseDaoImpl implements IReportByTheUnitDao {

    /**
     * 获取应发工资  和  实发工资  单位名称 查询分页
     * @param queryParam
     * @return
     */
    @Override
    public List<ReportByTheUnit> getWageMonthHistoryPage(PaginationDTO paginationDTO, Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append("select ai.id,ay.salary_month, ai.agency_name, nvl(sum(t2.s1), 0.00) theActualTotalWages , nvl(sum(t2.s2), 0.00)  shouldIssueTotalWages,t2.agencyid");
        /** 单位基础信息表 **/
        sql.append(" from (select  s1, s2, pet.agency_id agencyid, pet.person_type_code persontypecode ,")
                .append(" pet.merit_pay_code meritpaycode from ")
                /**工资月结表*/
                .append(TableName.SALARY_MONTH_END).append("  sme ")
                /**人员信息扩展表*/
                .append(" left join  ").append(TableName.PERSON_EXT).append("  pet ")
                .append(" on pet.person_id = sme.person_id")
                /**人员是否删除 工资是否停发*/
                .append(" where pet.is_deleted='0'  and pet.is_stop_salary=0  ")
                .append(" )  t2   ")
                /** 单位基础信息表 **/
                .append(" left join ").append(TableName.AGENCY_INFO).append("  ai")
                .append(" on t2.agencyid=ai.id  and ai.is_deleted='0'")
                /** 机构上报审核状态表 **/
                .append(" left join ").append(TableName.AGENCY_VERIFY).append("  ay")
                .append(" on ay.agency_id=t2.agencyid ");

        if(!StringUtils.isEmpty(queryParam) ) {
            //判断是否上报
            if("0".equals(queryParam.get("flag"))){
                sql.append(" where ay.is_salary_submit in (0,-9) ");
            }else{
                sql.append(" where ay.is_salary_submit =? ");
                paramList.add(queryParam.get("flag"));
            }

            //查询未月结的 未被删除的
            sql.append(" and ay.is_month_end='0' and ay.is_deleted=0");
            //查询人员类型
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and t2.persontypecode = ? ");
                paramList.add(queryParam.get("personType"));
            }
            //查询工资类型
            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
                sql.append(" and t2.meritpaycode = ? ");
                paramList.add(queryParam.get("salaryType"));
            }
            //判断是否有变化
            if (!StringUtils.isEmpty(queryParam.get("changesType"))) {
                if(queryParam.get("changesType").equals("0")){
                    sql.append(" and ay.is_salary_change = 0 ");
                } else {
                    sql.append(" and ay.is_salary_change != 0 ");
                }
            }
            // 根据id获取该id下的单位信息
            if(!StringUtils.isEmpty(queryParam.get("newId"))){

                String id = queryParam.get("newId").toString();
                sql.append(" and ( ai.parent_id = ? or ai.id = ? )");
                paramList.add(id);
                paramList.add(id);
            }else if(!StringUtils.isEmpty(queryParam.get("agencyId")) ){
                String id = queryParam.get("agencyId").toString();
                sql.append(" and ( ai.parent_id = ? or ai.id = ? )");
                paramList.add(id);
                paramList.add(id);
            }
        }
        //分组
        sql.append(" group by ai.id, ay.salary_month,ai.agency_name,t2.agencyid");
        //排序
        sql.append(" order by ai.create_time");
        String querySql = this.boxUpSql(sql.toString());
        //请求参数初始化
        Object[] params = this.boxUpParam(paramList.toArray(), paginationDTO);
        return this.getJdbcTemplate().query(querySql, new BeanPropertyRowMapper(ReportByTheUnit.class), params);
    }
    /**
     * 获取应发工资  和  实发工资  单位名称 查询分页total
     * @param queryParam
     * @return
     */
    @Override
    public int getWageMonthHistoryTotal(Map<String, Object> queryParam) {
        List<Object> paramList = new ArrayList<>();
        StringBuilder sql = new StringBuilder();
        sql.append(" select count(*) from (select  count(ai.agency_name)");
        /** 单位基础信息表 **/
        sql.append(" from (select  s1, s2, pet.agency_id agencyid, pet.person_type_code persontypecode ,")
                .append(" pet.merit_pay_code meritpaycode from ")
                //关联工资月结表
                .append(TableName.SALARY_MONTH_END).append("  sme ")
                //人员扩展信息表
                .append(" left join  ").append(TableName.PERSON_EXT).append("  pet ")
                .append(" on pet.person_id = sme.person_id")
                .append(" where pet.is_deleted='0' and pet.is_stop_salary='0' ")
                .append(" )  t2   ")
                /** 单位基础信息表 **/
                .append(" left join ").append(TableName.AGENCY_INFO).append("  ai")
                .append(" on t2.agencyid=ai.id  and ai.is_deleted='0'")
                /** 机构上报审核状态表 **/
                .append(" left join ").append(TableName.AGENCY_VERIFY).append("  ay")
                .append(" on ay.agency_id=t2.agencyid ");
        if(!StringUtils.isEmpty(queryParam) ) {
            //判断是否上报
            sql.append(" where ay.is_salary_submit = ? ");
            paramList.add(queryParam.get("flag"));
            //查询未月结的
            sql.append(" and ay.is_month_end='0' and ay.is_deleted=0");
            //查询人员类型
            if (!StringUtils.isEmpty(queryParam.get("personType"))) {
                sql.append(" and t2.persontypecode = ? ");
                paramList.add(queryParam.get("personType"));
            }
            //查询工资类型
            if (!StringUtils.isEmpty(queryParam.get("salaryType"))) {
                sql.append(" and t2.meritpaycode = ? ");
                paramList.add(queryParam.get("salaryType"));
            }
            //判断是否有变化
            if (!StringUtils.isEmpty(queryParam.get("changesType"))) {
                if(queryParam.get("changesType").equals("0")){
                    sql.append(" and ay.is_salary_change = 0");
                } else {
                    sql.append(" and ay.is_salary_change != 0 ");
                }
            }
            // 根据id获取该id下的单位信息
            if(!StringUtils.isEmpty(queryParam.get("newId"))) {

                String id = queryParam.get("newId").toString();
                sql.append(" and ( ai.parent_id = ? or ai.id = ? )");
                paramList.add(id);
                paramList.add(id);
            }else if(!StringUtils.isEmpty(queryParam.get("agencyId")) ){

                String id = queryParam.get("agencyId").toString();
                sql.append(" and ( ai.parent_id = ? or ai.id = ? )");
                paramList.add(id);
                paramList.add(id);
            }
        }
        //分组
        sql.append(" group by ai.id, ay.salary_month,ai.agency_name,t2.agencyid");
        //排序
        sql.append(" order by ai.create_time )  a");
        return this.getJdbcTemplate().queryForObject(sql.toString(), Integer.class, paramList.toArray());
    }
}
